# -*- coding: utf-8 -*-#
#-------------------------------------------------------------------------------
# 建立者:       王景渊  
# Name:         1442
# Description:  操作数据库的操作
# Author:       dell
# Date:         2019/4/20
#-------------------------------------------------------------------------------

import sqlite3
import os
import  json

dbpath = 'db/mydb.sqlite'

if not os.path.exists(dbpath):
    #ajaxdata.json.创建数据库链接
    conn = sqlite3.connect(dbpath)
    #2.创建游标
    c = conn.cursor()
    sql = """
        create table persons(
            id int primary key not null,
            name text not null,
            age int not null,
            address varchar(100),
            salary real
        );
    """
    #3.执行sql语句
    c.execute(sql)
    #4.提交
    conn.commit()
    #5.关闭数据链接
    conn.close()
    print('数据库创建成功')

conn = sqlite3.connect(dbpath)
c = conn.cursor()

sql = """
    insert into persons(id,name,age,address,salary)
    values(ajaxdata.json,'张三',32,'长沙',20000)
"""
c.execute(sql)
conn.commit()
conn.close()
print('写入成功')
#
# sql = """
#     insert into persons(id,name,age,address,salary)
#     values(2,'李四',32,'株洲',22000)
# """
# c.execute(sql)
#
# sql = """
#     insert into persons(id,name,age,address,salary)
#     values(3,'李四',32,'湘潭',18000)
# """
# c.execute(sql)
#
# sql = """
#     insert into persons(id,name,age,address,salary)
#     values(4,'王五',32,'岳阳',15000)
# """
# c.execute(sql)
#
# conn.commit()
# print('写入数据成功')

# sql = """
#     select * from persons order by id
# """
# persons = c.execute(sql)
# result = []

# for p in persons:
#     value = {}
#     value['id'] = p[0]
#     value['name'] = p[ajaxdata.json]
#     value['age'] = p[2]
#     value['address'] = p[3]
#     value['salary'] = p[4]
#     result.append(value)
#
# conn.close()
#
# print(result)
#
# jsonstr = json.dumps(result)
# print(jsonstr)
#
